Cloudflare D1

Get started · Cloudflare D1 docs

SQL Datebase

SQLite

https://www.youtube.com/watch?v=egBdW6vBIhM

建立資料庫

1. npx wrangler@latest d1 create [DATABASE]

2. 組建>儲存空間和資料庫>D1 SQL 資料庫>建立資料庫

綁定資料庫至Cloudflare Workers

1. 使用wrangler指令後會自動綁定

2. 複製建立後的相關資訊，貼至wrangler檔案內

code:wrangler.toml

d1_databases

binding = "DATABASE" # 可在Worker內使用env.DATABASE呼叫

database_name = "DATABASE"

database_id = "DATABASE_ID"

code:wrangler.jsonc

{

"$schema": "./node_modules/wrangler/config-schema.json",

"d1_databases": [

{

"binding": "DATABASE",

"database_name": "DATABASE",

"database_id": "DATABASE_ID"

}

]

}

binding名稱必須為合法的JavaScript變數

設定資料庫

建立schema.sql

Schema

範例

code:schema.sql

DROP TABLE IF EXISTS Customers;

CREATE TABLE IF NOT EXISTS Customers (CustomerId INTEGER PRIMARY KEY, CompanyName TEXT, ContactName TEXT);

INSERT INTO Customers (CustomerID, CompanyName, ContactName) VALUES (1, 'Alfreds Futterkiste', 'Maria Anders'), (4, 'Around the Horn', 'Thomas Hardy'), (11, 'Bs Beverages', 'Victoria Ashworth'), (13, 'Bs Beverages', 'Random Name');

本地測試

$ npx wrangler d1 execute DATABASE --local --file=./schema.sql

$ npx wrangler d1 execute DATABASE --local --command="SELECT * FROM Customers"

$ npm wrangler dev

線上佈署

$ npx wrangler d1 execute DATABASE --remote --file=./schema.sql

$ npx wrangler d1 execute DATABASE --remote --command="SELECT * FROM Customers"

$ npx wrangler deploy

在Worker中使用

code:index.ts

export default {

async fetch(request, env) {

const { pathname } = new URL(request.url);

if (pathname === "/api/beverages") {

// 在此修改DB綁定名稱

const { results } = await env.DATABASE

.prepare("SELECT * FROM Customers WHERE CompanyName = ?")

.bind("Bs Beverages")

.run();

return Response.json(results);

}

return new Response(

"Call /api/beverages to see everyone who works at Bs Beverages",

);

};

刪除資料庫

$ npx wrangler d1 delete DATABASE

在Hono上使用

Query D1 from Hono · Cloudflare D1 docs

確定已在wrangler上綁定

env.[DATABASE]會暴露在context上

透過c.env.[DATABASE]即可進行呼叫

code:js

import { Hono } from "hono";

// This ensures c.env.DB is correctly typed

type Bindings = {

DATABASE: D1Database;

};

const app = new Hono<{ Bindings: Bindings }>();

// Accessing D1 is via the c.env.YOUR_BINDING property

app.get("/query/users/:id", async (c) => {

const userId = c.req.param("id");

try {

let { results } = await c.env.DATABASE.prepare(

"SELECT * FROM users WHERE user_id = ?",

)

.bind(userId)

.run();

return c.json(results);

} catch (e) {

return c.json({ err: e.message }, 500);

}

});

// Export our Hono app: Hono automatically exports a

// Workers 'fetch' handler for you

export default app;

Prepared statement methods · Cloudflare D1 docs

prepare()

建立查詢語句

bind()

代入至參數化查詢中的對應變數

run()

執行查詢

first()

回傳第一行查詢結果